//The MIT License
//
//Copyright (c) <2011> <GEPAR - Grupo de Ensino e Pesquisa em Agroinformática e Rastreabilidade>
//
//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:
//
//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.
//
//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.

package core;

import com.sun.rowset.CachedRowSetImpl;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.sql.*;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import javax.sql.rowset.CachedRowSet;
import org.jasypt.util.text.*;

/**
 * Classe Data Acess Object referente a classe Tabulacao. É responsável pela manipulação
 * dos dados do banco local.
 */
public class TabulacaoDAO {

    /**
     * Objeto que provê conexão ativa com o banco de dados local.
     */
    Connection conexao = BancoDadosLocal.getInstancia().getConexao();

    public static final int erro_conexao = 0;

    public static final int erro_login = 1;

    public static final int erro_interno = 2;

    public static final int sucesso = 3;
    
    public static final int interrompida = 4;

    /**
     * Método para recuperar, por ordem alfabética, todos os produtos cadastrados no banco local.
     * @return ResultSet com todos os produtos cadastrados, ordenados alfabeticamente.
     */
    public ResultSet obterProdutos() throws Exception
    {
        // TODO: obter ordenados alfabeticamente
        String sqlObterProdutos = "SELECT * FROM produtos";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet produtos = sentencaSQL.executeQuery(sqlObterProdutos);

        return produtos;
    }

    /**
     * Método para recuperar, por ordem alfabética, todos os entrepostos cadastrados no banco local.
     * @return ResultSet com todos os entrepostos cadastrados, ordenados alfabeticamente.
     */
    public ResultSet obterEntrepostos() throws Exception
    {
        // TODO: obter ordenados alfabeticamente
        String sqlObterEntrepostos = "SELECT * FROM entrepostos";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet entrepostos = sentencaSQL.executeQuery(sqlObterEntrepostos);

        return entrepostos;
    }

    /**
     * Método para recuperar, por ordem alfabética, todos as classificações de qualidade cadastradas no banco local.
     * @return ResultSet com todos os produtos cadastrados, ordenados alfabeticamente.
     */
    public ResultSet obterClassificacoes() throws Exception
    {
        // TODO: obter ordenados alfabeticamente
        String sqlObterClassificacoes = "SELECT * FROM classificacoes";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet classificacoes = sentencaSQL.executeQuery(sqlObterClassificacoes);

        return classificacoes;
    }

    /**
     * Método para recuperar, por ordem alfabética, todos os tipos de caixa cadastrados no banco local.
     * @return ResultSet com todos os tipos de caixa cadastrados, ordenados alfabeticamente.
     * @throws Exception
     */
    public ResultSet obterTiposCaixa() throws Exception
    {
        // TODO: obter ordenados alfabeticamente
        String sqlObterTiposCaixa = "SELECT * FROM tipos_caixa";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet tiposCaixa = sentencaSQL.executeQuery(sqlObterTiposCaixa);

        return tiposCaixa;
    }

    /**
     * Método para recuperar, por ordem alfabética, todas as diferentes quantidades, usadas por caixa,
     * cadastradas no banco local.
     * @return ResultSet com todas as quantidades cadastradas, ordenadas de maneira crescente.
     */
    public ResultSet obterQtdesCaixa() throws Exception
    {
        // TODO: obter ordenadas de maneira crescente
        String sqlObterQtdesCaixa = "SELECT * FROM qtdes_caixa";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet qtdesCaixa = sentencaSQL.executeQuery(sqlObterQtdesCaixa);

        return qtdesCaixa;
    }
    
    /**
     * Método para recuperar, por ordem alfabética, todos os responsáveis por coletas cadastrados no 
     * banco local.
     * @return ResultSet com todos os responsáveis por coletas cadastrados, ordenados alfabeticamente.
     */
    public ResultSet obterResponsaveisCol() throws Exception
    {
        // TODO: obter ordenados alfabeticamente
        String sqlObterResponsaveisCol = "SELECT * FROM responsaveis_col";

        Statement sentencaSQL = this.conexao.createStatement();

        ResultSet responsaveisCol = sentencaSQL.executeQuery(sqlObterResponsaveisCol);

        return responsaveisCol;
    }

    public void gravarTabulacao(Tabulacao tabulacao) throws Exception
    {
        int idNovaTabul;
        idNovaTabul = inserirTabulacao(tabulacao.getConfigNovaTabul());
        tabulacao.setIdTabulacao(idNovaTabul);

        this.inserirPrecos(tabulacao);
        this.inserirEstatisticas(tabulacao);
    }

    private int inserirTabulacao(ConfigNovaTabul configTabul) throws Exception
    {
        int idTabulacao = 0;

        String sqlInserirTabulacao = "INSERT INTO tabulacoes_pen" +
                "(" +
                "id_produto, " +
                "id_entreposto, " +
                "id_classific, " +
                "id_tipo_caixa, " +
                "id_qtde_caixa, " +
                "id_resp_col, " +
                "id_resp_col2, " +
                "data_col" +
                ") " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        
        PreparedStatement prepStmt = this.conexao.prepareStatement(sqlInserirTabulacao, Statement.RETURN_GENERATED_KEYS);

        prepStmt.setInt(1, configTabul.getIdNomeProduto());
        prepStmt.setInt(2, configTabul.getIdEntreposto());
        prepStmt.setInt(3, configTabul.getIdClassific());
        prepStmt.setInt(4, configTabul.getIdMetricaCaixa());
        prepStmt.setInt(5, configTabul.getIdQtdeCaixa());
        prepStmt.setInt(6, configTabul.getIdRespColeta());
        prepStmt.setInt(7, configTabul.getIdRespColeta2());

        java.sql.Date dataSQL = new Date(configTabul.getDataColeta().getTime());
        prepStmt.setDate(8, dataSQL);

        prepStmt.executeUpdate();

        ResultSet rsGenKeys = prepStmt.getGeneratedKeys();
        if (rsGenKeys.next())
        {
            idTabulacao = rsGenKeys.getInt(1);
        }

        prepStmt.close();

        return idTabulacao;
    }

    private void inserirPrecos(Tabulacao tabulacao) throws Exception
    {
        String sqlInserirPreco = "INSERT INTO precos" +
                "(" +
                "id_tabulacao, " +
                "valor_caixa, " +
                "valor_unidade" +
                ") " +
                "VALUES(?, ?, ?)";

        PreparedStatement prepStmt = this.conexao.prepareStatement(sqlInserirPreco);

        // inserção dos preços
        LinkedList<Float> listaPrecosCaixa = tabulacao.getPrecosCaixa();
        LinkedList<Float> listaPrecosUnidade = tabulacao.getPrecosUnidade();
        int qtdePrecos = listaPrecosCaixa.size();

        prepStmt.setInt(1, tabulacao.getIdTabulacao());

        for (int i = 0; i < qtdePrecos; i++)
        {
            float precoAtualCaixa = listaPrecosCaixa.get(i);
            float precoAtualUnidade = listaPrecosUnidade.get(i);

            prepStmt.setFloat(2, precoAtualCaixa);
            prepStmt.setFloat(3, precoAtualUnidade);
            prepStmt.execute();
        }
    }

    private void inserirEstatisticas(Tabulacao tabulacao) throws Exception
    {
        String sqlInserirEstatisticas = "INSERT INTO estatisticas" +
                "(" +
                "id_tabulacao, " +
                "preco_max_c, " +
                "preco_max_u, " +
                "preco_min_c, " +
                "preco_min_u, " +
                "media_caixa, " +
                "media_unidade, " +
                "moda_caixa, " +
                "moda_unidade," +
                "mediana_caixa, " +
                "mediana_unidade" +
                ") " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        PreparedStatement psEstatisticas = this.conexao.prepareStatement(sqlInserirEstatisticas);

        psEstatisticas.setInt(1, tabulacao.getIdTabulacao());
        psEstatisticas.setFloat(2, tabulacao.getPrecoMaxCaixa());
        psEstatisticas.setFloat(3, tabulacao.getPrecoMaxUnidade());
        psEstatisticas.setFloat(4, tabulacao.getPrecoMinCaixa());
        psEstatisticas.setFloat(5, tabulacao.getPrecoMinUnidade());
        psEstatisticas.setFloat(6, tabulacao.getMediaCaixa());
        psEstatisticas.setFloat(7, tabulacao.getMediaUnidade());
        psEstatisticas.setFloat(8, tabulacao.getModaCaixa());
        psEstatisticas.setFloat(9, tabulacao.getModaUnidade());
        psEstatisticas.setFloat(10, tabulacao.getMedianaCaixa());
        psEstatisticas.setFloat(11, tabulacao.getMedianaUnidade());

        psEstatisticas.execute();
    }

    public ResultSet obterTabulacoesPendentes() throws Exception
    {
        String sqlObterTabulPen = " SELECT" +
	" tabul.id AS id_tabulacao," +
	" tabul.data_col," +
	" prod.nome AS nome_prod," +
	" entrep.nome AS nome_entrep" +
	" FROM" +
	" tabulacoes_pen tabul" +
	" LEFT JOIN produtos prod ON tabul.id_produto = prod.id" +
	" LEFT JOIN entrepostos entrep ON tabul.id_entreposto = entrep.id";

        Statement stmt = conexao.createStatement();

        try
        {
            ResultSet rs = stmt.executeQuery(sqlObterTabulPen);
            CachedRowSet cachedTabulacoes = new CachedRowSetImpl();
            cachedTabulacoes.populate(rs);

            return cachedTabulacoes;
        }
        catch(Exception e)
        {
            throw e;
        }
	finally
        {
            stmt.close();
        }
    }

    public void excluirTabulacaoPen(int idTabulacao) throws Exception
    {
        String sqlExcluirTabulacaoPen = "DELETE FROM tabulacoes_pen WHERE id = ?";

        PreparedStatement psExcluirTabulPen = this.conexao.prepareStatement(sqlExcluirTabulacaoPen);
        psExcluirTabulPen.setInt(1, idTabulacao);

        psExcluirTabulPen.execute();
    }

    /**
     * Estabelece uma conexão HTTP com o Hermes Web. Envia, por POST, os parâmetros passados. Retorna
     * a mensagem recebida do Hermes Web.
     * @param nomesParam Nomes dos parâmetros da requisição HTTP
     * @param valoresParam Valores dos parâmetros da requisição HTTP
     * @param enderecoHTTP Endereço HTTP para comunicação
     * @return Mensagem retornada pelo Hermes Web
     * @throws Exception Erro de conexão com o Hermes Web
     */
    private String comunicadorHTTP(ArrayList<String> nomesParam, ArrayList<String> valoresParam, String enderecoHTTP) throws Exception
    {
        String data = ""; // dados a serem enviados ao servidor
        String msg = ""; // mensagem que desejamos recuperar da resposta HTTP

        int tamanho = nomesParam.size();

        for (int i = 0; i < tamanho; i++)
        {
            // adicionando nome + valor à string que representa os dados a serem enviados ao servidor
            if (i > 0)
            {
                data += "&";
            }

            data += URLEncoder.encode(nomesParam.get(i), "UTF-8") + "=" +
                    URLEncoder.encode(valoresParam.get(i), "UTF-8");
        }

        // enviando os dados
        URL url = new URL(enderecoHTTP);
        URLConnection conn = url.openConnection();
        conn.setDoOutput(true);
        OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
        wr.write(data);
        wr.flush();

        // interpretando a resposta
        BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
        String line;
        while ((line = rd.readLine()) != null)
        {
            // verificamos se a linha atual tem a resposta esperada
            if (line.contains("<p>"))
            {
                // extraimos a mensagem desejada da resposta
                msg = line.replace("<p>", "");
                msg = msg.replace("</p>", "");
                msg = msg.trim();

                break;
            }
        }

        wr.close();
        rd.close();
        
        return msg;
    }
    
    /**
     * 
     * @param elementosOriginais Valor tipo texto plano.
     * @param senhaCriptografia Senha base para criptografia.
     * @return Texto já criptografado.
     */
    private String aplicarCriptografia(String textoOriginal, String senhaCriptografia)
    {
        String textoCriptografado;
        
        BasicTextEncryptor textEncryptor = new BasicTextEncryptor();
        textEncryptor.setPassword(senhaCriptografia);
        
        textoCriptografado = textEncryptor.encrypt(textoOriginal);
        
        return textoCriptografado;
    }
    
    public int enviarTabulacoesPen(String emailTabulador, String senhaTabulador)
    {
        String senhaCriptografia = "hermesweb031011";
        int respostaHermesWeb;
        String idTabulador;
        
        // verificação de email e senha
        try
        {
            String emailTabuladorCripto = this.aplicarCriptografia(emailTabulador, senhaCriptografia);
            String senhaTabuladorCripto = this.aplicarCriptografia(senhaTabulador, senhaCriptografia);
            
            ArrayList<String> nomesParam = new ArrayList<String>();
            ArrayList<String> valoresParam = new ArrayList<String>();
            
            nomesParam.add("email");
            valoresParam.add(emailTabuladorCripto);
            nomesParam.add("senha");
            valoresParam.add(senhaTabuladorCripto);
            
            String resposta = this.comunicadorHTTP(nomesParam, valoresParam, "http://www.fatecmogidascruzes.com.br/hermes/faces/autenticarTabulador.xhtml");
            respostaHermesWeb = Integer.parseInt(resposta.substring(0, 1));
            
            if (respostaHermesWeb != TabulacaoDAO.sucesso)
            {
                // erro de login ou erro interno
                return respostaHermesWeb;
            }
            else
            {
                // login realizado com sucesso; armazenamos o id do Tabulador
                idTabulador = resposta.substring(2);
            }
            
        } catch(Exception e)
        {
            return TabulacaoDAO.erro_conexao;
        }

        String sqlTabulacoesPendentes = "SELECT * FROM tabulacoes_pen";

        try
        {
            Statement stmtTabulacoesPen = this.conexao.createStatement();
            ResultSet rsTabulacoesPen = stmtTabulacoesPen.executeQuery(sqlTabulacoesPendentes);

            // cada tabulação pendente é enviada ao HermesWeb (via HTTP) para ser inserida no banco de dados
            while (rsTabulacoesPen.next())
            {
                ArrayList<String> nomesParam = new ArrayList<String>();
                ArrayList<String> valoresParam = new ArrayList<String>();

                // adicionamos o id do tabulador
                nomesParam.add("id_respt");
                valoresParam.add(idTabulador);
                
                // informações da tabulação
                nomesParam.add("id_produto");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_produto")));
                nomesParam.add("id_entreposto");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_entreposto")));
                nomesParam.add("id_classific");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_classific")));
                nomesParam.add("id_tipo_caixa");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_tipo_caixa")));
                nomesParam.add("id_qtde_caixa");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_qtde_caixa")));
                nomesParam.add("id_resp_col");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_resp_col")));
                nomesParam.add("id_resp_col2");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getInt("id_resp_col2")));
                nomesParam.add("data_col");
                valoresParam.add(String.valueOf(rsTabulacoesPen.getDate("data_col")));

                // recuperando todos os preços dessa tabulação do banco de dados local
                String sqlObterPrecos = "SELECT valor_caixa, valor_unidade FROM precos WHERE id_tabulacao = ?";
                PreparedStatement stmtPrecos = this.conexao.prepareStatement(sqlObterPrecos);
                stmtPrecos.setInt(1, rsTabulacoesPen.getInt("id"));
                ResultSet rsPrecos = stmtPrecos.executeQuery();

                String todosPrecosCaixa = "";
                String todosPrecosUnidade = "";

                // transformando os preços dessa tabulação em um parâmetro a ser enviado por HTTP
                while (rsPrecos.next())
                {
                    todosPrecosCaixa += String.valueOf(rsPrecos.getFloat("valor_caixa"));
                    todosPrecosUnidade += String.valueOf(rsPrecos.getFloat("valor_unidade"));
                    
                    todosPrecosCaixa += "|";
                    todosPrecosUnidade += "|";
                }
                
                // removemos o | inserido após o último preço
                todosPrecosCaixa = todosPrecosCaixa.substring(0, todosPrecosCaixa.length() - 1);
                todosPrecosUnidade = todosPrecosUnidade.substring(0, todosPrecosUnidade.length() - 1);

                nomesParam.add("precos_caixa");
                valoresParam.add(todosPrecosCaixa);
                nomesParam.add("precos_unidade");
                valoresParam.add(todosPrecosUnidade);

                String sqlObterEstatisticas = "SELECT * FROM estatisticas WHERE id_tabulacao = ?";
                PreparedStatement stmtEstatisticas = this.conexao.prepareStatement(sqlObterEstatisticas);
                stmtEstatisticas.setInt(1, rsTabulacoesPen.getInt("id"));
                ResultSet rsEstatisticas = stmtEstatisticas.executeQuery();

                // informações estatísticas da tabulação atual
                rsEstatisticas.next();
                nomesParam.add("preco_max_c");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("preco_max_c")));
                nomesParam.add("preco_max_u");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("preco_max_u")));
                nomesParam.add("preco_min_c");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("preco_min_c")));
                nomesParam.add("preco_min_u");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("preco_min_u")));
                nomesParam.add("media_caixa");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("media_caixa")));
                nomesParam.add("media_unidade");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("media_unidade")));
                nomesParam.add("moda_caixa");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("moda_caixa")));
                nomesParam.add("moda_unidade");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("moda_unidade")));
                nomesParam.add("mediana_caixa");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("mediana_caixa")));
                nomesParam.add("mediana_unidade");
                valoresParam.add(String.valueOf(rsEstatisticas.getFloat("mediana_unidade")));

                String resposta =
                        this.comunicadorHTTP(nomesParam, valoresParam, "http://www.fatecmogidascruzes.com.br/hermes/faces/inserirTabulacao.xhtml");

                respostaHermesWeb = Integer.parseInt(resposta);

                if (respostaHermesWeb != TabulacaoDAO.sucesso)
                {
                    // erro na inserção da tabulação atual; o processo é interrompido
                    return TabulacaoDAO.interrompida;
                }
                else
                {
                    // tabulação atual inserida com sucesso; podemos removê-la do BD local
                    String sqlRemoverTabulAtual = "DELETE FROM tabulacoes_pen WHERE id = ?";
                    PreparedStatement stmtRemoverTabulAtual = this.conexao.prepareStatement(sqlRemoverTabulAtual);
                    stmtRemoverTabulAtual.setInt(1, rsTabulacoesPen.getInt("id"));
                    stmtRemoverTabulAtual.execute();
                }  
            }
        } catch (Exception e)
        {
            // não foi possível completar a operação devido a um erro interno
            return TabulacaoDAO.erro_interno;
        }

        // processo totalmente finalizado com sucesso
        return TabulacaoDAO.sucesso;
    }
}
